package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import dbconn.DBConnect;
import model.Column;
/********
 * 栏目管理类
 * 栏目添加
 * 删除栏目
 * 栏目信息更新
 * 获取单个栏目信息
 * 获取现有所有栏目
 * @author Cyoung
 * date 05/12/2012
 *
 */
public class ColumnControl {
	public static boolean addColumn(Column project){
		DBConnect dbc = new DBConnect();
		String sql = "insert into bigcolumn (column) value(?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, project.getColumn());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	
	//delete project
	public static boolean deleteColumn(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from bigcolumn where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//update project
	public static boolean updateColumn(Column project,int id){
		DBConnect dbc = new DBConnect();
		String sql = "update bigcolumn set column=? where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, project.getColumn());
			dbc.setInt(2, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//get project information
	public static Column getColumn(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from bigcolumn where id=?";
		dbc.prepareStatement(sql);
		try{
			Column project = new Column();
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
			project.setColumn(rs.getString("column"));
			return project;
			}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List<Column> getAllColumns() {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List projects = new ArrayList<Column>();
		String sql = "select * from bigcolumn";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				Column project = new Column();
				project.setId(rs.getInt("id"));
				project.setColumn(rs.getString("column"));
				projects.add(project);
			}
		} catch (SQLException e) {
		        e.printStackTrace();
			System.out.println("select fail！\n" + e.getMessage());
			return null;
		} finally {
			dbc.close();
		}
		return projects;
	}

}
